EC2のユーザーデータでMySQL(MariaDB)をセットアップするときに文字化けの対策をする方法

こんにちは、臼田です。

みなさん、ユーザーデータ活用してますか？(挨拶

今回はちょっとした検証でユーザーデータを使ったMySQL(のつもりで使ってるけど実際はMariaDB)のセットアップでハマったのでその対応について共有します。

まえおき

ユーザーデータを使ってEC2上にMySQLをセットアップするのは通常あまりやるべきではありません。というかユーザーデータじゃなくてもEC2ではなくRDSなどを利用すべきです。

が、今回はちょっとした検証のために手抜きをしてユーザーデータを使っています。オススメしないのでそこのとこお願いします。

背景

ちょっとした検証のためにEC2の環境をセットアップするCloudFormationのテンプレートを作ることにしました。MySQLの環境も必要だったのですが、ローカルのMySQLに接続するプログラムが用意されていたので、ユーザーデータの中で必要なパッケージを入れつつデータベースの作成やテーブルの作成などを行うことにしました。

実際に構築されたEC2を確認していると、プログラムが適切に動作しないことに気づきました。

原因調査

ユーザーデータでセットアップしていた内容は大体以下の通りです。細かいところは突っ込まないでください。

#!/bin/bash
yum update -y
yum install -y httpd mysql perl-DBI perl-DBD-MySQL mariadb-server
systemctl start mariadb
systemctl enable mariadb

mysql -u root <<EOF
CREATE DATABASE aaaaa;
CREATE USER 'aaaaa_ro'@'localhost' IDENTIFIED BY '******************';
SHOW GRANTS for aaaaa_ro@localhost;
USE 'aaaaa'
CREATE TABLE \`☹☺☻\`(id int, name varchar(256), pass varchar(256));
INSERT INTO \`☹☺☻\` VALUES (1, 'admin', '12345');
GRANT SELECT ON aaaaa.\`☹☺☻\` to aaaaa_ro@localhost;
EOF

ここで大事なところは、テーブル名がマルチバイトであることです。本来は絶対やりたくないことですが、要件がこうなっていたので仕方なくやっています。

試行錯誤しながら無事セットアップできるところまで作り込んだのですが、いざ動かしてみるとデータベースとのやり取りでエラーが発生しました。どうもテーブルが存在していないようでした。

そこで様子を見に行くと以下のようになっていました。

MariaDB [aaaaa]> show tables;
+--------------------+
| Tables_in_aaaaa    |
+--------------------+
| ☹☺☻          |
+--------------------+
1 row in set (0.00 sec)

テーブル名が文字化けしていました。検証中、該当のセットアップコマンドはユーザーデータではなく事前に直接シェルでアクセスして動作確認しており、そのときには問題なくマルチバイトの文字列でテーブルが作成・表示されていました。

したがって文字コードの問題であると認識しました。

試しにユーザーデータでmysqlクライアントから接続した後に文字コードを確認してみます。

#!/bin/bash
…省略…
echo "chara設定なし"
mysql -u root -e "show variables like 'chara%';"

この結果の確認は/var/log/cloud-init-output.logで可能です。適当にechoしておくと探しやすくて便利です。

chara設定なし
Variable_name   Value
character_set_client    latin1
character_set_connection        latin1
character_set_database  latin1
character_set_filesystem        binary
character_set_results   latin1
character_set_server    latin1
character_set_system    utf8
character_sets_dir      /usr/share/mysql/charsets/

ほとんどのcharacter_setがlatin1になっています。テーブル作成のクエリが適切な文字コードで発行されていないことが原因でした。

対策を調べたところ、mysqlクライアントで接続時に文字コードを指定する--default-character-set=utf8mb4オプションをつけてあげれば解決できそうでした。

試してみましょう。

#!/bin/bash
…省略…
echo "chara設定あり"
mysql -u root --default-character-set=utf8mb4 -e "show variables like 'chara%';"

chara設定あり
Variable_name   Value
character_set_client    utf8mb4
character_set_connection        utf8mb4
character_set_database  latin1
character_set_filesystem        binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir      /usr/share/mysql/charsets/

これでutf8mb4が適切に利用されました。

これを設定したユーザーデータでテーブルを作成します。

#!/bin/bash
yum update -y
yum install -y httpd mysql perl-DBI perl-DBD-MySQL mariadb-server
systemctl start mariadb
systemctl enable mariadb

mysql -u root --default-character-set=utf8mb4 <<EOF
CREATE DATABASE aaaaa;
CREATE USER 'aaaaa_ro'@'localhost' IDENTIFIED BY '******************';
SHOW GRANTS for aaaaa_ro@localhost;
USE 'aaaaa'
CREATE TABLE \`☹☺☻\`(id int, name varchar(256), pass varchar(256));
INSERT INTO \`☹☺☻\` VALUES (1, 'admin', '12345');
GRANT SELECT ON aaaaa.\`☹☺☻\` to aaaaa_ro@localhost;
EOF

動作を確認してみます。

MariaDB [aaaaa]> show tables;
+-----------------+
| Tables_in_aaaaa |
+-----------------+
| ☹☺☻             |
+-----------------+
1 row in set (0.00 sec)

これで無事動作しました。

まとめ

ユーザーデータを使ってMySQL(MariaDB)のセットアップをマルチバイトで行うと文字化けしてしまうので、文字コードの状態には注意しましょう。

まあでも、そんなことよりもマルチバイトのテーブル名ダメ、絶対。